﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using GMS.Utils;

/// <summary>
///MultiDbSql 的摘要说明
/// </summary>
public static class MultiDbSql
{
    //public MultiDbSql()
    //{
    //    //
    //    //TODO: 在此处添加构造函数逻辑
    //    //
    //}

    public static string GetTotalNumSql(string sql, int dbtype)
    {
        if (sql == null)
                return null;
            if ((int)EnumHelper.E_DbType.DB_SQL == dbtype)
                return "select count(*) as num from (" + sql + ") counttab"; //sqlserver 需要加 counttab
            else //ACCESS 是以下语句，oracle and mysql也是么？待证明
                return "select count(*) as num from (" + sql + ")";
    }

    public static string GetDefiniteSql(string sql, int start, int end, int dbtype, string strkey, string tabCode)
    {
        if (sql == null)
                return null;
        if (((int)EnumHelper.E_DbType.DB_ACCESS == dbtype) || ((int)EnumHelper.E_DbType.DB_SQL == dbtype))
        {
            if (string.IsNullOrEmpty(strkey) || string.IsNullOrEmpty(tabCode))
                return null;
             //下一句用SELECT TOP XXX 代替 原来sql中的 SELECT 字符串。
            string strS = "SELECT TOP ";
            if (sql.ToUpper().IndexOf("DISTINCT") > 0)
            {
                sql = sql.ToUpper().Replace("DISTINCT", "");
                strS = "SELECT DISTINCT TOP ";
            }

            string str = strS + (end - start).ToString() + " " + sql.Substring(sql.ToUpper().IndexOf("SELECT") + 6, sql.Length - 6);

            //下面的2行作用：因为access数据库不支持select top 0 XXXX from XXX 必须从1开始，
            //所以当start是0的时候，就不加后面的 not in (select top " 子句了，直接select top xxx
            if (0 == start)
                return str;

            if(str.ToUpper().IndexOf(" ORDER ") == -1) //无order by子句
            {
                //对于一张表连接本身，例如TAB_SysFunModule表因为内容有多级关系，需要连接自身，那么下面主键strkey
                //如果不加表名的话，数据库不知道strkey是属于哪个表的（因为有两只或以上的表），所以需要添加左连接
                //的那张表的临时表名，一般都用字符a
                if(str.ToUpper().IndexOf(" WHERE ") == -1)//没有where子句
                {
                    str = str + " WHERE " + tabCode + "." + strkey + " not in (select top " + start.ToString() + " " + strkey 
                        + " from " + tabCode.ToString() + ")";
                }
                else
                {
                    str = str + " and " + tabCode + "." + strkey + " not in (select top " + start.ToString() + " " + strkey
                       + " from " + tabCode.ToString() + ")";
                }
            }
            else            
            {
                string substr = str.Substring(0, str.ToUpper().IndexOf(" ORDER "));
                string suborder = str.Substring(str.ToUpper().IndexOf(" ORDER "), str.Length - str.ToUpper().IndexOf(" ORDER "));
                if (str.ToUpper().IndexOf(" WHERE ") == -1)//没有where子句
                {
                    str = substr + " WHERE " + tabCode + "." + strkey + " not in (select top " + start.ToString() + " " + strkey
                       + " from " + tabCode.ToString() + " " + suborder + ")" + suborder;
                }
                else
                {
                    str = substr + " and " + tabCode + "." + strkey + " not in (select top " + start.ToString() + " " + strkey
                      + " from " + tabCode.ToString() + " " + suborder + ")" + suborder;
                }
    
            }
           
            //string str = "select top " + (end - start).ToString() + 
            return str;
        }
        else if ((int)EnumHelper.E_DbType.DB_ORACLE == dbtype)
        {            
            return "select * from ( select row_.*, rownum rownum_ from ("
                + sql + ") row_ where rownum <= " + end + ") where rownum_ > " + start;
        }
        else
                return null;
    }
}
